package cromwell.database.migration.metadata.table

import cromwell.database.migration.metadata.MetadataCustomSql
import MetadataCustomSql._

/**
  * Transform data from the EXECUTION table to metadata
  */
class ExecutionTableMigration extends MetadataCustomSql {

  override def queries: Array[String] = {
    Array(
      s"""INSERT INTO METADATA_JOURNAL (
        WORKFLOW_EXECUTION_UUID,
        METADATA_KEY,
        CALL_FQN,
        JOB_SCATTER_INDEX,
        JOB_RETRY_ATTEMPT,
        METADATA_VALUE,
        METADATA_VALUE_TYPE,
        METADATA_TIMESTAMP
      )
        SELECT
        WORKFLOW_EXECUTION_UUID,
      'start',
    CALL_FQN,
    IDX,
    ATTEMPT,
    DATE_FORMAT(e.START_DT, '%Y-%m-%dT%T.%f$Offset'),
    'string',
    NOW()
    FROM TMP_EXECUTION_MIGRATION e
    JOIN WORKFLOW_EXECUTION we ON we.WORKFLOW_EXECUTION_ID = e.WORKFLOW_EXECUTION_ID
    WHERE
    e.START_DT IS NOT NULL;"""
    ,
    """INSERT INTO METADATA_JOURNAL (
      WORKFLOW_EXECUTION_UUID,
      METADATA_KEY,
      CALL_FQN,
      JOB_SCATTER_INDEX,
      JOB_RETRY_ATTEMPT,
      METADATA_VALUE,
      METADATA_VALUE_TYPE,
      METADATA_TIMESTAMP
    )
    SELECT
    WORKFLOW_EXECUTION_UUID,
    'backend',
    CALL_FQN,
    IDX,
    ATTEMPT,
    e.BACKEND_TYPE,
    'string',
    NOW()
    FROM TMP_EXECUTION_MIGRATION e
    JOIN WORKFLOW_EXECUTION we ON we.WORKFLOW_EXECUTION_ID = e.WORKFLOW_EXECUTION_ID;"""
    ,
    s"""INSERT INTO METADATA_JOURNAL (
      WORKFLOW_EXECUTION_UUID,
      METADATA_KEY,
      CALL_FQN,
      JOB_SCATTER_INDEX,
      JOB_RETRY_ATTEMPT,
      METADATA_VALUE,
      METADATA_VALUE_TYPE,
      METADATA_TIMESTAMP
    )
    SELECT
    WORKFLOW_EXECUTION_UUID,
    'end',
    CALL_FQN,
    IDX,
    ATTEMPT,
    DATE_FORMAT(e.END_DT, '%Y-%m-%dT%T.%f$Offset'),
    'string',
    NOW()
    FROM TMP_EXECUTION_MIGRATION e
    JOIN WORKFLOW_EXECUTION we ON we.WORKFLOW_EXECUTION_ID = e.WORKFLOW_EXECUTION_ID
    WHERE
    e.END_DT IS NOT NULL;"""
    ,
    """INSERT INTO METADATA_JOURNAL (
      WORKFLOW_EXECUTION_UUID,
      METADATA_KEY,
      CALL_FQN,
      JOB_SCATTER_INDEX,
      JOB_RETRY_ATTEMPT,
      METADATA_VALUE,
      METADATA_VALUE_TYPE,
      METADATA_TIMESTAMP
    )
    SELECT
    WORKFLOW_EXECUTION_UUID,
    'executionStatus',
    CALL_FQN,
    IDX,
    ATTEMPT,
    e.STATUS,
    'string',
    NOW()
    FROM TMP_EXECUTION_MIGRATION e
    JOIN WORKFLOW_EXECUTION we ON we.WORKFLOW_EXECUTION_ID = e.WORKFLOW_EXECUTION_ID;"""
    ,
    """INSERT INTO METADATA_JOURNAL (
      WORKFLOW_EXECUTION_UUID,
      METADATA_KEY,
      CALL_FQN,
      JOB_SCATTER_INDEX,
      JOB_RETRY_ATTEMPT,
      METADATA_VALUE,
      METADATA_VALUE_TYPE,
      METADATA_TIMESTAMP
    )
    SELECT
    WORKFLOW_EXECUTION_UUID,
    'returnCode',
    CALL_FQN,
    IDX,
    ATTEMPT,
    e.RC,
    'int',
    NOW()
    FROM TMP_EXECUTION_MIGRATION e
    JOIN WORKFLOW_EXECUTION we ON we.WORKFLOW_EXECUTION_ID = e.WORKFLOW_EXECUTION_ID
    WHERE e.RC IS NOT NULL;"""
    ,
    s"""INSERT INTO METADATA_JOURNAL (
      WORKFLOW_EXECUTION_UUID,
      METADATA_KEY,
      CALL_FQN,
      JOB_SCATTER_INDEX,
      JOB_RETRY_ATTEMPT,
      METADATA_VALUE,
      METADATA_VALUE_TYPE,
      METADATA_TIMESTAMP
    )
    SELECT
    WORKFLOW_EXECUTION_UUID,
    'cache:allowResultReuse',
    CALL_FQN,
    IDX,
    ATTEMPT,
    CASE e.ALLOWS_RESULT_REUSE WHEN 1 THEN 'true' ELSE 'false' END,
    'boolean',
    NOW()
    FROM TMP_EXECUTION_MIGRATION e
    JOIN WORKFLOW_EXECUTION we ON we.WORKFLOW_EXECUTION_ID = e.WORKFLOW_EXECUTION_ID;"""
    ,
    s"""INSERT INTO METADATA_JOURNAL (
      WORKFLOW_EXECUTION_UUID,
      METADATA_KEY,
      CALL_FQN,
      JOB_SCATTER_INDEX,
      JOB_RETRY_ATTEMPT,
      METADATA_VALUE,
      METADATA_VALUE_TYPE,
      METADATA_TIMESTAMP
    )
    SELECT
    WORKFLOW_EXECUTION_UUID,
    'preemptible',
    CALL_FQN,
    IDX,
    ATTEMPT,
    CASE ra.ATTRIBUTE_VALUE >= ATTEMPT WHEN 1 THEN 'true' ELSE 'false' END,
    'boolean',
    NOW()
    FROM TMP_EXECUTION_MIGRATION e
    JOIN WORKFLOW_EXECUTION we ON we.WORKFLOW_EXECUTION_ID = e.WORKFLOW_EXECUTION_ID
    LEFT JOIN RUNTIME_ATTRIBUTES ra ON e.EXECUTION_ID = ra.EXECUTION_ID AND ra.ATTRIBUTE_NAME = 'preemptible'
    WHERE ra.ATTRIBUTE_VALUE IS NOT NULL;"""
    ,
    """INSERT INTO METADATA_JOURNAL (
      WORKFLOW_EXECUTION_UUID,
      METADATA_KEY,
      CALL_FQN,
      JOB_SCATTER_INDEX,
      JOB_RETRY_ATTEMPT,
      METADATA_TIMESTAMP
    )
    SELECT
    WORKFLOW_EXECUTION_UUID,
    'runtimeAttributes',
    CALL_FQN,
    IDX,
    ATTEMPT,
    '1900-01-01'
    FROM TMP_EXECUTION_MIGRATION e
    JOIN WORKFLOW_EXECUTION we ON we.WORKFLOW_EXECUTION_ID = e.WORKFLOW_EXECUTION_ID;"""
    ,
    """INSERT INTO METADATA_JOURNAL (
      WORKFLOW_EXECUTION_UUID,
      METADATA_KEY,
      CALL_FQN,
      JOB_SCATTER_INDEX,
      JOB_RETRY_ATTEMPT,
      METADATA_TIMESTAMP
    )
    SELECT
    WORKFLOW_EXECUTION_UUID,
    'inputs',
    CALL_FQN,
    IDX,
    ATTEMPT,
    '1900-01-01'
    FROM TMP_EXECUTION_MIGRATION e
    JOIN WORKFLOW_EXECUTION we ON we.WORKFLOW_EXECUTION_ID = e.WORKFLOW_EXECUTION_ID;"""
    ,
    """INSERT INTO METADATA_JOURNAL (
      WORKFLOW_EXECUTION_UUID,
      METADATA_KEY,
      CALL_FQN,
      JOB_SCATTER_INDEX,
      JOB_RETRY_ATTEMPT,
      METADATA_TIMESTAMP
    )
    SELECT
    WORKFLOW_EXECUTION_UUID,
    'outputs',
    CALL_FQN,
    IDX,
    ATTEMPT,
    '1900-01-01'
    FROM TMP_EXECUTION_MIGRATION e
    JOIN WORKFLOW_EXECUTION we ON we.WORKFLOW_EXECUTION_ID = e.WORKFLOW_EXECUTION_ID;"""
    ,
    """INSERT INTO METADATA_JOURNAL (
      WORKFLOW_EXECUTION_UUID,
      METADATA_KEY,
      CALL_FQN,
      JOB_SCATTER_INDEX,
      JOB_RETRY_ATTEMPT,
      METADATA_TIMESTAMP
    )
    SELECT
    WORKFLOW_EXECUTION_UUID,
    'executionEvents[]',
    CALL_FQN,
    IDX,
    ATTEMPT,
    '1900-01-01'
    FROM TMP_EXECUTION_MIGRATION e
    JOIN WORKFLOW_EXECUTION we ON we.WORKFLOW_EXECUTION_ID = e.WORKFLOW_EXECUTION_ID;"""
    )
  }

  override def getConfirmationMessage: String = "Execution Table migration complete."
}
